from xmlrpclib import ServerProxy
from SimpleXMLRPCServer import SimpleXMLRPCServer
from datetime import datetime
from collections import namedtuple
import pyodbc
pyodbc.lowercase = True

RECORD_RESOURCE = 'Digital Recording'
MOODLE_DOCDESC = 'Moodle'
RESDATA_PREFIX = r'resdata\!'

Ordinals = ('0th', '1st', '2nd', '3rd', '4th', 'last')
DaysOfWeek = ((None,        '?'),
			  ('monday',    'M'),
			  ('tuesday',   'T'),
			  ('wednesday', 'W'),
			  ('thursday',  'R'),
			  ('friday',    'F'),
			  ('saturday',  'S'),
			  ('sunday',    'U'))

Session = namedtuple('Session', ('srcid',
								 'title',
								 'label',
								 'owner',
								 'email',
								 'room',
								 'start',
								 'stop',
								 'recur'))

def concat(seq, sep=''):
	return sep.join(seq)

class Repository:
	def __init__(self, dsn, host=None, user=None, pswd=None):
		self.dsn = dsn
		self.host = host
		self.user = user
		self.pswd = pswd
		self.lastfetch = self.lastend = datetime.min
		
	def Connect(self):
		if self.host is None:
			self.proxy = TPSBackEnd()
		else:
			url = "http://{user}:{pswd}@{host}/RPC2".format(**vars(self))
			self.proxy = ServerProxy(url, allow_none=True, use_datetime=True)
			
	def FetchAbs(self, begin, end, room):
		return self.proxy.Fetch(self.dsn, begin, end, room, None, None)
	
	def FetchIncr(self, length, room):
		begin = datetime.now()
		end = begin + length
		res = self.proxy.Fetch(self.dsn, begin, end, room, 
							   self.lastfetch, self.lastend)
		self.lastfetch = begin
		self.lastend = end
		return res

FETCH_UPDATE = """
SELECT transdate, transtime FROM booklog
ORDER BY transdate DESC, transtime DESC"""

FETCH_SESSN1 = """
SELECT act.actnumber, dat.datenumber, act.name, act.project, 
       act.client, cli.email, act.prime, act.repeattype,
       dat.date, dat.time, dat.enddate, dat.endtime,
       act.repnumber, act.week, act.weekday, act.monday, 
       act.tuesday, act.wednesday, act.thursday, 
       act.friday, act.saturday, act.sunday
FROM  {0}activity act, {0}dates dat, {0}client cli, {0}resact res
WHERE act.actnumber = dat.actnumber AND act.client = cli.client
      AND act.actnumber = res.actnumber AND res.resource = '{1}'
      AND act.tentative <> 4 AND act.inactive = 0"""
FETCH_SESSN2 = """
      AND act.prime = ?"""
FETCH_SESSN3 = """
      AND (dat.date > ? or dat.date = ? and dat.time >= ?)"""
FETCH_SESSN4 = """
      AND (dat.enddate > ? or dat.enddate = ? and dat.endtime >= ?)"""
FETCH_SESSN5 = """
      AND (dat.date < ? or dat.date = ? and dat.time < ?)
ORDER BY dat.date, dat.time"""

FETCH_REPT = """
SELECT repnumber, week, weekday, monday, tuesday, wednesday,
       thursday, friday, saturday, sunday
FROM  {0}activity WHERE actnumber = ?""".format(RESDATA_PREFIX)

FETCH_NOTE = """
SELECT notes FROM {0}actnotes WHERE actnumber = ?""".format(RESDATA_PREFIX)

FETCH_DOCS = """
SELECT filename FROM {0}docs WHERE number = ?
       AND description = '{1}'""".format(RESDATA_PREFIX, MOODLE_DOCDESC)

class TPSBackEnd:
	def Fetch(self, dsn, begin, end, room, lastfetch, lastend):
		conn = pyodbc.connect(dsn=dsn, autocommit=True)
		curs = conn.cursor()
		# second cursor used to fetch from actnotes and docs,
		# not currently used
		if 0:
			cur2 = conn.cursor()
		# Incremental mode is off when lastfetch is None, otherwise lastfetch
		# should be set to the datetime of the most recent fetch and lastend
		# should be set to the datetime of the end of last fetch window.
		incr = False
		if lastfetch:
			# If the DB has changed since the last fetch time, we will
			# return all events between begin and end times. Otherwise,
			# only return the events between the last fetch end time and 
			# the current end time.
			print 'Executing:', FETCH_UPDATE
			row = curs.execute(FETCH_UPDATE).fetchone()
			print 'Returned ', curs.rowcount, ' rows'
			if row and datetime.combine(*row) < lastfetch:
				incr = True
				begin = lastend
		# Assemble SQL statement parts based on if we are selecting
		# a single room, and also if we are making an incremental fetch.
		# Incremental fetch chooses events where starttime is between begin
		# and end times in order to not have overlap, whereas absolute fetch
		# chooses events where endtime > begin and starttime < end in order
		# to catch currently running events. 
		sql = (FETCH_SESSN1 +
			   (FETCH_SESSN2 if room else '') +
			   (FETCH_SESSN3 if incr else FETCH_SESSN4) +
			   FETCH_SESSN5).format(RESDATA_PREFIX, RECORD_RESOURCE)
		parms = ((room,) if room else ()) + (begin,)*3 + (end,)*3
		print 'Executing:', sql, parms
		curs.execute(sql, parms)
		print 'Returned', curs.rowcount, 'rows'
		result = []
		for row in curs:
			print 'hi!'
			# This part tries to figure out the repetition type, i.e. 'MWF'.
			# It is imperfect because of how ResSched handles changes to
			# bookings. Any true value indicates that the session repeats.
#			if row.repeattype:
#				recur = cur2.execute(FETCH_REPT, row.actnumber).fetchone()
#			else:
#				recur = True
			if row.repeattype == 2:   # Irregular
				recur = 'irregular'
			elif row.repeattype == 3: # Fixed days
				recur = '{}d'.format(row.repnumber)
			elif row.repeattype == 4: # Fixed weeks
				recur = '{}w'.format(row.repnumber)
			elif row.repeattype == 5: # Fixed months
				recur = '{}m'.format(row.repnumber)
			elif row.repeattype == 6: # Set days each week 
				recur = concat(char for day, char in DaysOfWeek[1:] if getattr(row, day))
			elif row.repeattype == 7: # Specific weekday of the month
				recur = '{} {}'.format(Ordinals[row.week], DaysOfWeek[row.weekday][1])
			else:
				recur = False
			# This part will fetch the list of links to Moodle courses,
			# if we decide to implement that.
			if 0:
				links = [row.filename for row in cur2.execute(FETCH_DOCS, row.actnumber)]
			# This part would be used if we needed any data from the
			# Notes field, like e.g. expiration date
			if 0:
				actnote = cur2.execute(FETCH_NOTE, row.actnumber).fetchone()
				if actnote:
					foo = [line.partition(':')[2].trim() 
								for line in actnote.notes.splitlines()
												if line.startswith('foo:')]
			# Fix up title, strip trailing '*' added by ResSched and remove
			# any comments in parentheses added by ETS.
			head, sep, tail = row.name.partition('(')
			comm, sep, tail = tail.rpartition(')')
			title = head.strip() + tail if sep else comm
			if title.endswith('*'):
				title = title.rstrip('*')
				recur = recur or True
			# build session instance
			print title, row.client, row.prime
			item = Session(srcid = (row.actnumber, row.datenumber),
						   title = title,
						   label = row.project,
						   owner = row.client,
						   email = row.email,
						   room  = row.prime,
						   start = datetime.combine(row.date, row.time),
						   stop  = datetime.combine(row.enddate, row.endtime),
						   recur = recur)
			result.append(item)
		if lastfetch:
			return incr, result
		else:
			return result

def TPSServer(host, port):
	server = SimpleXMLRPCServer((host, port), allow_none=True)
	server.register_instance(TPSBackEnd())
	server.serve_forever()

def testit():
	from datetime import date, time, timedelta
	d1 = datetime.combine(date.today(), time())
	d2 = d1 + timedelta(1)
	room = 'DL POC B07 (E03)'
	rss = Repository('ETS newver')
	rss.Connect()
	return rss.FetchAbs(d1, d2, room)

import sys

def testsql(dsn, sql):
	conn = pyodbc.connect(dsn=dsn, autocommit=True)
	curs = conn.cursor()
	print datetime.now()
	curs.execute(sql)
	print '+{:-^10}'.format('') * len(curs.description) + '+'
	print concat('|{:^10.10}'.format(col[0]) for col in curs.description) + '|'
	print '+{:-^10}'.format('') * len(curs.description) + '+'
	for row in curs:
		print concat('|{:10.10}'.format(str(item)) for item in row) + '|'
	print '+{:-^10}'.format('') * len(curs.description) + '+'
	print datetime.now()

if __name__ == '__main__':
	dsn = sys.argv[1]
	sql = file(sys.argv[2]).read()
	print "Using DSN '{}' to execute:\n{}\n".format(dsn, sql)
	testsql(dsn, sql)
	
